CREATE PROCEDURE [dbo].[STIAddInvoice]
    @InvoiceId BIGINT,
    @UserId SMALLINT,
    @InvoiceType SMALLINT = 0
AS
BEGIN
    BEGIN TRY

        DECLARE @ReferenceExists BIT = 1;
        DECLARE @Id UNIQUEIDENTIFIER;
        DECLARE @Status SMALLINT = -1;
        SELECT @Status = [Status],
               @Id = Id
        FROM dbo.STITransaction
        WHERE InvoiceId = @InvoiceId;

        IF @InvoiceType= 6 --  Return Invoice           
           AND NOT EXISTS --- if invoice can insert into  to STITransaction
        (
            SELECT 1
            FROM dbo.STITransaction STI
            WHERE Type = 5
                  AND STI.Status = 1
                  AND STI.InvoiceId =
                  (
                      SELECT TOP (1)
                             Id_Havaleh
                      FROM dbo.headH WITH (NOLOCK)
                      WHERE Kind_Flag IN ( 5, 7, 9 )
                            AND Nu_Forosh =
                            (
                                SELECT TOP (1)
                                       Nu_Forosh
                                FROM dbo.headH WITH (NOLOCK)
                                WHERE Kind_Flag IN ( 0, 6, 8 )
                                      AND Id_Havaleh = @InvoiceId
                            )
                      ORDER BY Tarikh
                  )
        )
            SET @ReferenceExists = 0;
        IF @Status = -1
        BEGIN
            IF @ReferenceExists = 1
                INSERT INTO dbo.STITransaction
                (
                    InvoiceId,
                    [Status],
                    [Type],
                    CreatedBy
                )
                VALUES
                (@InvoiceId, 0, @InvoiceType, @UserId);
            ELSE ---  if returned invoice can not be send to DB 
            BEGIN
                INSERT INTO dbo.STITransaction
                (
                    InvoiceId,
                    [Status],
                    [Type],
                    ResultMessage,
                    CreatedBy
                )
                VALUES
                (@InvoiceId, 2, @InvoiceType, N'    ', @UserId);
            END;
            SELECT 0 ResultCode;
        END;
        ELSE IF @Status IN ( 2, 3 )
                AND @ReferenceExists = 1
        BEGIN
            UPDATE dbo.STITransaction
            SET Status = 0,
                ResultMessage = NULL,
                ErrorCode = NULL,
                JsonResult = NULL,
                ResultId = NULL,
                CreatedOn = GETDATE(),
                CreatedBy = @UserId
            WHERE Id = @Id;
            SELECT 1 ResultCode;
        END;
        ELSE IF @Status IN ( 5 )
                AND @ReferenceExists = 1
        BEGIN
            UPDATE dbo.STITransaction
            SET Status = 4,
                ResultMessage = NULL,
                ErrorCode = NULL,
                JsonResult = NULL,
                ResultId = NULL,
                CreatedOn = GETDATE(),
                CreatedBy = @UserId
            WHERE Id = @Id;
            SELECT 1 ResultCode;
        END;
        ELSE IF @Status = 1
            SELECT 2 ResultCode;
        ELSE IF @Status = 4
            SELECT 3 ResultCode;
        ELSE IF @Status = 0
            SELECT 4 ResultCode;

    END TRY
    BEGIN CATCH
        INSERT INTO dbo.STITransaction
        (
            InvoiceId,
            [Status],
            [Type],
            CreatedBy
        )
        VALUES
        (@InvoiceId, 10, @InvoiceType, 200);
    END CATCH;
END;
